package com.jianggy;

import com.alibaba.fastjson.JSON;
import net.sf.jsqlparser.JSQLParserException;
import net.sf.jsqlparser.expression.Expression;
import net.sf.jsqlparser.expression.operators.relational.EqualsTo;
import net.sf.jsqlparser.expression.operators.relational.InExpression;
import net.sf.jsqlparser.expression.operators.relational.ItemsList;
import net.sf.jsqlparser.parser.CCJSqlParserManager;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.statement.Statement;
import net.sf.jsqlparser.statement.select.Join;
import net.sf.jsqlparser.statement.select.PlainSelect;
import net.sf.jsqlparser.statement.select.Select;
import net.sf.jsqlparser.statement.select.SelectItem;
import net.sf.jsqlparser.util.TablesNamesFinder;
import org.apache.calcite.config.Lex;
import org.apache.calcite.sql.SqlNode;
import org.apache.calcite.sql.dialect.OracleSqlDialect;
import org.apache.calcite.sql.parser.SqlParseException;
import org.apache.calcite.sql.parser.SqlParser;

import java.io.StringReader;
import java.util.List;

/**
 * Hello world!
 *
 */
public class App {
    public static void main(String[] args) throws  SqlParseException {

        // Sql语句
        String sql = "select * from emps where id = 1";
        // 解析配置
        SqlParser.Config mysqlConfig = SqlParser.configBuilder().setLex(Lex.MYSQL).build();
        // 创建解析器
        SqlParser parser = SqlParser.create(sql, mysqlConfig);
        // 解析sql
        SqlNode sqlNode = parser.parseQuery();
        System.out.println(JSON.toJSONString(sqlNode));
        // 还原某个方言的SQL
        System.out.println(sqlNode.toSqlString(OracleSqlDialect.DEFAULT));
    }

    public static void parser1() throws JSQLParserException {
        // 多表SQL
        String sql  = "SELECT t1.a , t1.b  FROM tab1 AS t1 JOIN tab2 t2 ON t1.user_id  = t2.user_id where t1.c=1";

        // 1.解析表名
        CCJSqlParserManager parserManager = new CCJSqlParserManager();
        // 解析SQL为Statement对象
        Statement statement = parserManager.parse(new StringReader(sql));
        // 创建表名发现者对象
        TablesNamesFinder tablesNamesFinder = new TablesNamesFinder();
        // 获取到表名列表
        List<String> tableNameList = tablesNamesFinder.getTableList(statement);
        // 循环打印解析到的表名 tab1 tab2
        tableNameList.forEach(tableName -> System.out.println("表名："+tableName));
        // 2.解析查询元素=》 列，函数等
        Select select = (Select) CCJSqlParserUtil.parse(sql);
        PlainSelect plainSelect = (PlainSelect) select.getSelectBody();
        List<SelectItem> selectItems = plainSelect.getSelectItems();
        // t1.a , t1.b
        selectItems.forEach(name -> System.out.println("查询的列："+name));

        // 3.解析WHERE条件
        PlainSelect plainSelectWhere = (PlainSelect) ((Select) CCJSqlParserUtil.parse(sql)).getSelectBody();
        EqualsTo equalsTo = (EqualsTo) plainSelectWhere.getWhere();
        Expression leftExpression = equalsTo.getLeftExpression();
        Expression rightExpression = equalsTo.getRightExpression();
        System.err.println("查询条件名称:"+leftExpression+"---查询条件值:"+rightExpression);

        // 4.解析Join
        List<Join> joins = plainSelect.getJoins();
        joins.forEach(e -> {
            Expression onExpression = e.getOnExpression();
            // 获取ON 表达式 t1.user_id = t2.user_id
            System.err.println(onExpression);
        });

        // 5.解析IN
        String SQL_IN = "SELECT *  FROM tableName WHERE ID IN (8,9,10)";
        PlainSelect plainSelectIn = (PlainSelect) ((Select) CCJSqlParserUtil.parse(SQL_IN)).getSelectBody();
        InExpression inExpression = (InExpression) plainSelectIn.getWhere();
        ItemsList rightItemsList = inExpression.getRightItemsList();
        // (8, 9, 10)
        System.err.println(rightItemsList);

        plainSelect.getDistinct();
        plainSelect.getFetch();
        plainSelect.getFirst();
        plainSelect.getGroupBy();

    }
}
